from utils.DBService import sqlquery

import decimal, os

KUCUN_SQL="""
WITH cte AS (
    select ts.departname,cl.cailiaoName,kc.kucun,ROW_NUMBER() over(PARTITION by ts.departname,cl.cailiaoName order by kc.createDateTime desc) as rn
from WZKuCun as kc,t_s_depart as ts,WZCaiLiaoNameDict as cl where kc.departId = ts.ID and kc.cailiaoguid = cl.cailiaoNo
and ts.ID in ('{}')
{}
)
SELECT * 
FROM cte
WHERE rn = 1"""

CAILIAONO_SQL = """select cailiaoNo from WZCaiLiaoNameDict where parentNode in (select cailiaoNo from WZCaiLiaoNameDict where cailiaoName in (\'{}\'))"""

DEP_SQL = """WITH  dept
    AS (SELECT  *
        FROM   t_s_depart
        WHERE  parentdepartid = '{}'
        UNION ALL
        SELECT  d.*
        FROM   t_s_depart d
            INNER JOIN dept ON d.parentdepartid = dept.id
						 )
  SELECT id
  FROM  dept"""

def inventoryStatic(Args,user):
    print('Inventory参数:\n\t', Args)
    depId = Args['depId']
    dates = Args['dates']
    materials = Args['materials']

    # query jinchang
    depId = '\',\''.join(depId.split(','))
    if depId == 'All':
        deps = sqlquery(DEP_SQL.format(user))
        print('SQL:\n\t', DEP_SQL.format(user))
        print(deps)
        depsid = []
        for dep in deps:
            depsid.append(dep[0])
        depId = '\',\''.join(depsid)
    materials = '\',\''.join(materials.split(','))
    print(materials)
    print('SQL:\n\t', CAILIAONO_SQL.format(materials))
    cl = sqlquery(CAILIAONO_SQL.format(materials))
    print(cl)
    if cl.__len__() > 0:
        cls = []
        for c in cl:
            cls.append(c[0])
        materials = '\',\''.join(cls)
        clWhere = 'and cl.cailiaoNo in (\'{}\') '.format(materials)
    else:
        clWhere = 'and cailiaoName like \'%{}%\' '.format(materials) if materials != 'All' else ''
    kucunSql = KUCUN_SQL.format(depId, clWhere)
    print('KC_SQL:\n\t', kucunSql)
    cailiaoData = sqlquery(kucunSql)
    clData = {}
    for cailiao in cailiaoData:
        depName, clName, weight = '', '', 0
        if os.environ.get("ENV_NAME")=='1':
            depName, clName, weight = cailiao[0], cailiao[1], str(
                decimal.Decimal(cailiao[2]).quantize(decimal.Decimal('0.00')))
        else:
            depName, clName, weight = cailiao[0], cailiao[1].encode('latin1').decode('gbk'), str(decimal.Decimal(cailiao[2]).quantize(decimal.Decimal('0.00')))
        print(depName, clName, weight)
        if depName not in clData:
            clData[depName] = {"materials":[clName], 'weights':[weight]}
        else:
            clData[depName]['materials'].append(clName)
            clData[depName]['weights'].append(weight)
    return clData


functionConf = {
    "name": "inventoryStatic",
    "description": "Query the latest inventory data of materials based on their outbound ID, time, and material name. Be careful not to disturb",
    "parameters": {
        "type": "object",
        "properties": {
            "depId": {
                "type": "string",
                "description": "Department ID, multiple departments separated by commas in English. If no department is specified for the problem, it defaults to All，eg：f6323b406b4fab1b016bacabdb9f01d3,f6323b406c1829b3016c269d3b2c0036",
            },
            "dates": {
                "type": "string",
                "description": "The time of the material, using yyyy-MM-dd format, with multiple dates separated by commas, for example:2023-07-14,2023-07-15"
            },
            "materials": {
                "type": "string",
                "description": "Item names, multiple items separated by commas, all items using 'All', such as fly ash, cement"
            }
        },
        "required": [
            "depId",
            "dates",
            "materials"
        ]
    }
}